package learn_0403; /**
 * @author haomin
 * @date 2022/04/02 19:37
 **/
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.*;
import java.util.Scanner;


// 连接我的 MySQL 数据库
public class Main {
    public static void main(String[] args) throws SQLException {
        MysqlDataSource db = new MysqlDataSource();
        // 1. 告诉客户端，MySQL服务器在哪
        // 通过告诉 地址 + 端口
        db.setServerName("localhost");
        db.setPort(3306);
        // 2. 认证
        db.setUser("root");
        db.setPassword("123456");
        //  设置默认库
        db.setDatabaseName("0331_library");
        // 3. 其他配置
        db.setUseSSL(false);
        db.setCharacterEncoding("utf-8");
        db.setServerTimezone("Asia/Shanghai");


        //已设默认库，写执行语句
        // a. 查询
        String sql = "select * from books";
        // 4. 通过网络和MySQL服务器建立连接
        try (Connection c = db.getConnection()) {
            // 5. 发送给MySQL服务器我们的语句
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                // 6. executeQuery 执行查询
                // 7. ResultSet接收(需要输出)
                try (ResultSet rs = ps.executeQuery()) {
                    // 8. 遍历、检索、打印集中的数据
                    while (rs.next()) {
                        String rid = rs.getString(1);
                        String name = rs.getString(2);
                        String stock = rs.getString(3);
                        String total = rs.getString(4);
                        System.out.printf("%s\t%s\t%s\t%s\n",rid,name,stock,total);
                    }
                }
            }
        }

        try(Connection c = db.getConnection()){
            // b. 新增
            String sql1 = "insert into boods (name,stock,total) values ('呼吸'，10，10)";
            try(PreparedStatement ps = c.prepareStatement(sql1)){
                //不需要输出
                ps.executeUpdate();
            }

            Scanner scanner = new Scanner(System.in);
            String bookName  = scanner.next();
            // 但是会有sql注入的风险(若输入 ‘ or 1=1 or name = ')
            String sql2 = "select * from readers where name = '" + bookName + "'";
            try(PreparedStatement ps = c.prepareStatement(sql2)){
                try(ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        String rid = rs.getString(1);
                        String name = rs.getString(2);
                        String stock = rs.getString(3);
                        String total = rs.getString(4);
                        System.out.printf("%s\t%s\t%s\t%s\n",rid,name,stock,total);
                    }
                }
            }

            //为了避免sql注入风险，所有用 ？
            String bookName1  = scanner.next();
            String sql4 = "select * from books where name = ?";
            try(PreparedStatement ps = c.prepareStatement(sql4)) {
                ps.setString(1,bookName1);
                try(ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        String rid = rs.getString(1);
                        String name = rs.getString(2);
                        String stock = rs.getString(3);
                        String total = rs.getString(4);
                        System.out.printf("%s\t%s\t%s\t%s\n",rid,name,stock,total);
                    }
                }
            }

            // 得到插入的自增值
            String nameb = scanner.next();
            String sql3 = "insert into readers (name) values (?)";
            try(PreparedStatement ps = c.prepareStatement(sql3, Statement.RETURN_GENERATED_KEYS)){
                ps.setString(1,nameb);
                ps.executeUpdate();

                // 得到插入值的自增值
                try(ResultSet rs = ps.getGeneratedKeys()){
                    rs.next();
                    int rid = rs.getInt(1);
                    System.out.println("新插入了一个reader:" + rid +"\t"+nameb);
                }
            }
        }
    }
}
